package cn.kain.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.kain.dao.GoodsDao;
import cn.kain.entity.Goods;
import cn.kain.util.DBTool;

/**
 * @author kai
 * @email kain.wong@foxmail.com
 * @Data 2020年1月23日 上午12:57:18
 * @Description TODO
 * 
 */
public class GoodsDaoImpl implements GoodsDao {

	private Connection conn = null;
	private PreparedStatement psmt = null;

	@Override
	public void addGoods(Goods g) {
		String sql = "INSERT INTO goods "
				+ "(goods_num,goods_name,type_id,supplier,gross,num,unit,price) values (?,?,?,?,?,?,?,?)";
		try {
			conn = DBTool.getConnection();
			psmt = conn.prepareStatement(sql);
			psmt.setString(1, g.getGoodsNum());
			psmt.setString(2, g.getGoodsName());
			psmt.setInt(3, g.getTypeId());
			psmt.setString(4, g.getSupplier());
			psmt.setDouble(5, g.getGross());
			psmt.setInt(6, g.getNum());
			psmt.setString(7, g.getUnit());
			psmt.setDouble(8, g.getPrice());
			psmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBTool.close(conn);
		}

	}

	@Override
	public void deleteGoodsById(int id) {
		String sql = "DELETE FROM goods where id=?";
		try {
			conn = DBTool.getConnection();
			psmt = conn.prepareStatement(sql);
			psmt.setInt(1, id);
			psmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBTool.close(conn);
		}

	}

	@Override
	public Goods selectGoodsById(int id) {
		String sql = "SELECT * FROM goods WHERE id=?";
		Goods good = new Goods();
		try {
			conn = DBTool.getConnection();
			psmt = conn.prepareStatement(sql);
			psmt.setInt(1, id);
			ResultSet rs = psmt.executeQuery();
			if (rs.next()) {
				good.setId(rs.getInt("id"));
				good.setGoodsNum(rs.getString("goods_num"));
				good.setGoodsName(rs.getString("goods_name"));
				good.setTypeId(rs.getInt("type_id"));
				good.setSupplier(rs.getString("supplier"));
				good.setGross(rs.getDouble("gross"));
				good.setNum(rs.getInt("num"));
				good.setUnit(rs.getString("unit"));
				good.setPrice(rs.getDouble("price"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBTool.close(conn);
		}
		return good;
	}

	@Override
	public List<Goods> selectAllGoods() {
		String sql = "SELECT * FROM goods";
		List<Goods> list = new ArrayList<Goods>();
		try {
			conn = DBTool.getConnection();
			psmt = conn.prepareStatement(sql);
			ResultSet rs = psmt.executeQuery();
			while (rs.next()) {
				Goods good = new Goods();
				good.setId(rs.getInt("id"));
				good.setGoodsNum(rs.getString("goods_num"));
				good.setGoodsName(rs.getString("goods_name"));
				good.setTypeId(rs.getInt("type_id"));
				good.setSupplier(rs.getString("supplier"));
				good.setGross(rs.getDouble("gross"));
				good.setNum(rs.getInt("num"));
				good.setUnit(rs.getString("unit"));
				good.setPrice(rs.getDouble("price"));
				list.add(good);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBTool.close(conn);
		}
		return list;
	}

	@Override
	public List<Goods> findGoodsByNumAndName(String goods_num,String goods_name) {
		StringBuffer sb = new StringBuffer("SELECT * FROM goods WHERE 1=1");
		List<Goods> list  = new ArrayList<Goods>();
		try {
			if (!"".equals(goods_num)) {
				sb.append(" and goods_num like '%"+goods_num+"%'");
			}
			if (!"".equals(goods_name)) {
				sb.append(" and goods_name like '%"+goods_name+"%'");
			}
			conn = DBTool.getConnection();
			psmt = conn.prepareStatement(sb.toString());
			ResultSet rs = psmt.executeQuery();
			while (rs.next()) {
				Goods good = new Goods();
				good.setId(rs.getInt("id"));
				good.setGoodsNum(rs.getString("goods_num"));
				good.setGoodsName(rs.getString("goods_name"));
				good.setTypeId(rs.getInt("type_id"));
				good.setSupplier(rs.getString("supplier"));
				good.setGross(rs.getDouble("gross"));
				good.setNum(rs.getInt("num"));
				good.setUnit(rs.getString("unit"));
				good.setPrice(rs.getDouble("price"));
				list.add(good);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBTool.close(conn);
		}
		return list;
	}

	@Override
	public List<Goods> findGoodsByTypeId(int typeId) {
		String sql = "SELECT * FROM goods WHERE type_id=?";
		List<Goods> list = new ArrayList<Goods>();
		try {
			conn = DBTool.getConnection();
			psmt = conn.prepareStatement(sql);
			psmt.setInt(1, typeId);
			ResultSet rs = psmt.executeQuery();
			while (rs.next()) {
				Goods good = new Goods();
				good.setId(rs.getInt("id"));
				good.setGoodsNum(rs.getString("goods_num"));
				good.setGoodsName(rs.getString("goods_name"));
				good.setTypeId(rs.getInt("type_id"));
				good.setSupplier(rs.getString("supplier"));
				good.setGross(rs.getDouble("gross"));
				good.setNum(rs.getInt("num"));
				good.setUnit(rs.getString("unit"));
				good.setPrice(rs.getDouble("price"));
				list.add(good);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBTool.close(conn);
		}
		return list;
	}

}
